package com.servicesDirectory.dao.government;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.servicesDirectory.common.exception.FunctionalException;
import com.servicesDirectory.common.exception.SystemException;
import com.servicesDirectory.common.vo.BaseGovernmentVO;


public class BaseGovernmentDAOImpl implements BaseGovernmentDAO {

    protected static Connection conn = null;
    protected String qr;
    public BaseGovernmentDAOImpl() {
    }

    protected static void initConnection() throws SQLException,
            ClassNotFoundException {

        if (conn == null) {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            conn = DriverManager.getConnection(
                    "jdbc:odbc:MockPJ_ServiceDirectory", "sa", "12345678");
        }
    }

    @Override
    public List<BaseGovernmentVO> listGovOfficeRegion(String filter,
            boolean include) throws FunctionalException, SystemException {
        qr="";
        conn = null;
        Statement stmt = null;
        List<BaseGovernmentVO> governments = null;
        BaseGovernmentVO government = null;


        try {
            if (include) {
                if (filter.equals("ABCDE")) {
                    qr = "SELECT gor.GovOfficeRegionID,gor.GovOfficeRegionName,gor.[Description],gor.IsActive,c.CountyName FROM GovernmentOfficeRegion gor INNER JOIN County c ON c.CountryID = gor.CountryID AND c.CountyID = gor.CountyID WHERE GovOfficeRegionName LIKE 'A%' OR GovOfficeRegionName LIKE 'B%' OR GovOfficeRegionName LIKE 'C%'OR GovOfficeRegionName LIKE 'D%' OR GovOfficeRegionName LIKE 'E%'";
                } else if (filter.equals("FGHIJ")) {
                    qr = "SELECT gor.GovOfficeRegionID,gor.GovOfficeRegionName,gor.[Description],gor.IsActive,c.CountyName "
                            + "FROM GovernmentOfficeRegion gor INNER JOIN County c "
                            + "ON c.CountryID = gor.CountryID AND c.CountyID = gor.CountyID "
                            + "WHERE GovOfficeRegionName LIKE 'F%' "
                            + "OR GovOfficeRegionName LIKE 'G%' "
                            + "OR GovOfficeRegionName LIKE 'H%' "
                            + "OR GovOfficeRegionName LIKE 'I%' "
                            + "OR GovOfficeRegionName LIKE 'J%' ";
                } else if (filter.equals("KLMN")) {
                    qr = "SELECT gor.GovOfficeRegionID,gor.GovOfficeRegionName,gor.[Description],gor.IsActive,c.CountyName "
                            + "FROM GovernmentOfficeRegion gor INNER JOIN County c "
                            + "ON c.CountryID = gor.CountryID AND c.CountyID = gor.CountyID "
                            + "WHERE GovOfficeRegionName LIKE 'K%' "
                            + "OR GovOfficeRegionName LIKE 'L%' "
                            + "OR GovOfficeRegionName LIKE 'M%' "
                            + "OR GovOfficeRegionName LIKE 'N%' ";
                } else if (filter.equals("OPQR")) {
                    qr = "SELECT gor.GovOfficeRegionID,gor.GovOfficeRegionName,gor.[Description],gor.IsActive,c.CountyName "
                            + "FROM GovernmentOfficeRegion gor INNER JOIN County c "
                            + "ON c.CountryID = gor.CountryID AND c.CountyID = gor.CountyID "
                            + "WHERE GovOfficeRegionName LIKE 'O%' "
                            + "OR GovOfficeRegionName LIKE 'P%' "
                            + "OR GovOfficeRegionName LIKE 'Q%' "
                            + "OR GovOfficeRegionName LIKE 'R%' ";
                } else if (filter.equals("STUV")) {
                    qr = "SELECT gor.GovOfficeRegionID,gor.GovOfficeRegionName,gor.[Description],gor.IsActive,c.CountyName "
                            + "FROM GovernmentOfficeRegion gor INNER JOIN County c "
                            + "ON c.CountryID = gor.CountryID AND c.CountyID = gor.CountyID "
                            + "WHERE GovOfficeRegionName LIKE 'S%' "
                            + "OR GovOfficeRegionName LIKE 'T%' "
                            + "OR GovOfficeRegionName LIKE 'U%' "
                            + "OR GovOfficeRegionName LIKE 'V%' ";
                } else if (filter.equals("WXYZ")) {
                    qr = "SELECT gor.GovOfficeRegionID,gor.GovOfficeRegionName,gor.[Description],gor.IsActive,c.CountyName "
                            + "FROM GovernmentOfficeRegion gor INNER JOIN County c "
                            + "ON c.CountryID = gor.CountryID AND c.CountyID = gor.CountyID "
                            + "WHERE GovOfficeRegionName LIKE 'W%' "
                            + "OR GovOfficeRegionName LIKE 'X%' "
                            + "OR GovOfficeRegionName LIKE 'Y%' "
                            + "OR GovOfficeRegionName LIKE 'Z%' ";
                } else if (filter.equals("0-9")) {
                    qr = "SELECT gor.GovOfficeRegionID,gor.GovOfficeRegionName,gor.[Description],gor.IsActive,c.CountyName "
                            + "FROM GovernmentOfficeRegion gor INNER JOIN County c "
                            + "ON c.CountryID = gor.CountryID AND c.CountyID = gor.CountyID "
                            + "WHERE GovOfficeRegionName LIKE '0%' "
                            + "OR GovOfficeRegionName LIKE '1%' "
                            + "OR GovOfficeRegionName LIKE '2%' "
                            + "OR GovOfficeRegionName LIKE '3%' "
                            + "OR GovOfficeRegionName LIKE '4%' "
                            + "OR GovOfficeRegionName LIKE '5%' "
                            + "OR GovOfficeRegionName LIKE '6%' "
                            + "OR GovOfficeRegionName LIKE '7%' "
                            + "OR GovOfficeRegionName LIKE '8%' "
                            + "OR GovOfficeRegionName LIKE '9%' ";
                } else {
                    qr = "SELECT gor.GovOfficeRegionID,gor.GovOfficeRegionName,gor.[Description],gor.IsActive,c.CountyName FROM GovernmentOfficeRegion gor INNER JOIN County c ON c.CountryID = gor.CountryID AND c.CountyID = gor.CountyID";
                }
            } else{
                if (filter.equals("ABCDE")) {
                    qr = "SELECT gor.GovOfficeRegionID,gor.GovOfficeRegionName,gor.[Description],gor.IsActive,c.CountyName FROM GovernmentOfficeRegion gor INNER JOIN County c ON c.CountryID = gor.CountryID AND c.CountyID = gor.CountyID AND IsActive = 1 WHERE GovOfficeRegionName LIKE 'A%' OR GovOfficeRegionName LIKE 'B%' OR GovOfficeRegionName LIKE 'C%'OR GovOfficeRegionName LIKE 'D%' OR GovOfficeRegionName LIKE 'E%'";
                } else if (filter.equals("FGHIJ")) {
                    qr = "SELECT gor.GovOfficeRegionID,gor.GovOfficeRegionName,gor.[Description],gor.IsActive,c.CountyName "
                            + "FROM GovernmentOfficeRegion gor INNER JOIN County c "
                            + "ON c.CountryID = gor.CountryID AND c.CountyID = gor.CountyID "
                            + " AND IsActive = 1 WHERE GovOfficeRegionName LIKE 'F%' "
                            + "OR GovOfficeRegionName LIKE 'G%' "
                            + "OR GovOfficeRegionName LIKE 'H%' "
                            + "OR GovOfficeRegionName LIKE 'I%' "
                            + "OR GovOfficeRegionName LIKE 'J%' ";
                } else if (filter.equals("KLMN")) {
                    qr = "SELECT gor.GovOfficeRegionID,gor.GovOfficeRegionName,gor.[Description],gor.IsActive,c.CountyName "
                            + "FROM GovernmentOfficeRegion gor INNER JOIN County c "
                            + "ON c.CountryID = gor.CountryID AND c.CountyID = gor.CountyID "
                            + " AND IsActive = 1 WHERE GovOfficeRegionName LIKE 'K%' "
                            + "OR GovOfficeRegionName LIKE 'L%' "
                            + "OR GovOfficeRegionName LIKE 'M%' "
                            + "OR GovOfficeRegionName LIKE 'N%' ";
                } else if (filter.equals("OPQR")) {
                    qr = "SELECT gor.GovOfficeRegionID,gor.GovOfficeRegionName,gor.[Description],gor.IsActive,c.CountyName "
                            + "FROM GovernmentOfficeRegion gor INNER JOIN County c "
                            + "ON c.CountryID = gor.CountryID AND c.CountyID = gor.CountyID "
                            + " AND IsActive = 1 WHERE GovOfficeRegionName LIKE 'O%' "
                            + "OR GovOfficeRegionName LIKE 'P%' "
                            + "OR GovOfficeRegionName LIKE 'Q%' "
                            + "OR GovOfficeRegionName LIKE 'R%' ";
                } else if (filter.equals("STUV")) {
                    qr = "SELECT gor.GovOfficeRegionID,gor.GovOfficeRegionName,gor.[Description],gor.IsActive,c.CountyName "
                            + "FROM GovernmentOfficeRegion gor INNER JOIN County c "
                            + "ON c.CountryID = gor.CountryID AND c.CountyID = gor.CountyID "
                            + " AND IsActive = 1 WHERE GovOfficeRegionName LIKE 'S%' "
                            + "OR GovOfficeRegionName LIKE 'T%' "
                            + "OR GovOfficeRegionName LIKE 'U%' "
                            + "OR GovOfficeRegionName LIKE 'V%' ";
                } else if (filter.equals("WXYZ")) {
                    qr = "SELECT gor.GovOfficeRegionID,gor.GovOfficeRegionName,gor.[Description],gor.IsActive,c.CountyName "
                            + "FROM GovernmentOfficeRegion gor INNER JOIN County c "
                            + "ON c.CountryID = gor.CountryID AND c.CountyID = gor.CountyID "
                            + " AND IsActive = 1 WHERE GovOfficeRegionName LIKE 'W%' "
                            + "OR GovOfficeRegionName LIKE 'X%' "
                            + "OR GovOfficeRegionName LIKE 'Y%' "
                            + "OR GovOfficeRegionName LIKE 'Z%' ";
                } else if (filter.equals("0-9")) {
                    qr = "SELECT gor.GovOfficeRegionID,gor.GovOfficeRegionName,gor.[Description],gor.IsActive,c.CountyName "
                            + "FROM GovernmentOfficeRegion gor INNER JOIN County c "
                            + "ON c.CountryID = gor.CountryID AND c.CountyID = gor.CountyID "
                            + " AND IsActive = 1 WHERE GovOfficeRegionName LIKE '0%' "
                            + "OR GovOfficeRegionName LIKE '1%' "
                            + "OR GovOfficeRegionName LIKE '2%' "
                            + "OR GovOfficeRegionName LIKE '3%' "
                            + "OR GovOfficeRegionName LIKE '4%' "
                            + "OR GovOfficeRegionName LIKE '5%' "
                            + "OR GovOfficeRegionName LIKE '6%' "
                            + "OR GovOfficeRegionName LIKE '7%' "
                            + "OR GovOfficeRegionName LIKE '8%' "
                            + "OR GovOfficeRegionName LIKE '9%' ";
                } else {
                    qr = "SELECT gor.GovOfficeRegionID,gor.GovOfficeRegionName,gor.[Description],gor.IsActive,c.CountyName FROM GovernmentOfficeRegion gor INNER JOIN County c ON c.CountryID = gor.CountryID AND c.CountyID = gor.CountyID AND IsActive = 1 ";
                }
            }



            /*
             * Initialize DB connection
             */
            initConnection();
            /*
             * Retrieve data from DB
             */
            stmt = conn.createStatement();

            ResultSet rs = stmt.executeQuery(qr);

            governments = new LinkedList<BaseGovernmentVO>();

            while (rs.next()) {
                int govOfficeID = rs.getInt("GovOfficeRegionID");
                String govOfficeName = rs.getString("GovOfficeRegionName");
                String govDescription = rs.getString("Description");
                int intIsActive = rs.getInt(4);
                String govCountyName = rs.getString("CountyName");

                boolean isActive = true;
                String stringActive;
                if (intIsActive == 1) {
                    isActive = true;
                } else {
                    isActive = false;
                }

                if (isActive) {
                    stringActive = "Yes";
                } else {
                    stringActive = "No";
                }

                government = new BaseGovernmentVO(govOfficeID, govOfficeName,
                        govDescription, isActive, govCountyName, stringActive);
                governments.add(government);
            }
        } catch (SQLException ex) {
            Logger.getLogger(BaseGovernmentDAOImpl.class.getName()).log(
                    Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(BaseGovernmentDAOImpl.class.getName()).log(
                    Level.SEVERE, null, ex);
        } finally {
            /*
             * Close all streams
             */
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    Logger.getLogger(BaseGovernmentDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    Logger.getLogger(BaseGovernmentDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return governments;
    }

    @Override
    public BaseGovernmentVO detailGovOfficeRegion(int govID)
            throws FunctionalException, SystemException {
        conn = null;
        Statement stm = null;
        BaseGovernmentVO vo = null;
        ResultSet rs;
        try {
            initConnection();
            stm = conn.createStatement();

            String qr = "SELECT gor.GovOfficeRegionID,gor.GovOfficeRegionName,gor.[Description],gor.IsActive,c.CountyName FROM GovernmentOfficeRegion gor INNER JOIN County c ON c.CountryID = gor.CountryID AND c.CountyID = gor.CountyID WHERE GovOfficeRegionID = " + govID;

            rs = stm.executeQuery(qr);
            while (rs.next()) {
                int govId = rs.getInt(1);
                String govName = rs.getString(2);
                String govDescription = rs.getString(3);
                int govActive = rs.getInt(4);
                boolean boolActive;
                if (govActive == 1) {
                    boolActive = true;
                } else {
                    boolActive = false;
                }

                String countyName = rs.getString(5);

                String stringActive;

                if (govActive == 1) {
                    stringActive = "Yes";
                } else {
                    stringActive = "No";
                }

                vo = new BaseGovernmentVO(govId, govName, govDescription, boolActive, countyName, stringActive);
            }
            return vo;
        } catch (SQLException ex) {
            Logger.getLogger(BaseGovernmentDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(BaseGovernmentDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            /*
             * Close all streams
             */
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    Logger.getLogger(BaseGovernmentDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    Logger.getLogger(BaseGovernmentDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return vo;
    }
}
